﻿-- =============================================
-- Populates the SecurityAccess Table
-- =============================================

IF '$(StaticData)' = '1'
	PRINT 'Creating/Updating SecurityAccess data...'
GO

IF '$(StaticData)' = '1'
BEGIN
	SET NOCOUNT ON

	SET IDENTITY_INSERT dbo.SecurityAccess ON

	DECLARE @SecurityID INT,
		@Title NVARCHAR(100),
		@Description NVARCHAR(512),
		@HierachySpecific BIT 

	SELECT @SecurityID = -1,
		@Title = 'User-CreateSelf',
		@Description = 'Users with access to the system have the ability to create the contact associated to thier account, if removed when a new users attempts to log into the system they will unable to until an administrator links a contact to the login.'
		, @HierachySpecific = 0

	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )
	
	SELECT @SecurityID = -11,
		@Title = 'Login-Edit',
		@Description = 'Enable or Disable Login access to the system'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -12,
		@Title = 'Login-Delete',
		@Description = 'Provides ability to remove Logins, or Windows users or Groups from accessing the system'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -20,
		@Title = 'Role-View',
		@Description = 'View roles and security granted to them'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -21,
		@Title = 'Login-Audit',
		@Description = 'View the change history of Roles'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -22,
		@Title = 'Role-Add',
		@Description = 'Create new custom roles'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -23,
		@Title = 'Role-Edit',
		@Description = 'Edit Role Security Access'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -24,
		@Title = 'Role-Membership',
		@Description = 'Edit Role memberships on Logins, Windows Users, or Windows Groups'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	SELECT @SecurityID = -25,
		@Title = 'Role-Delete',
		@Description = 'Provide abilty to remove Custom Roles'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
			, SAC_HiearchySpecific = @HierachySpecific 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription, SAC_HiearchySpecific )
		VALUES(@SecurityID, @Title, @Description, @HierachySpecific )

	--Delete Contact Access for now
	DELETE FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID > 100

/* MAB - Contact Persmissions need to be Altered 
	SELECT @SecurityID = -102,
		@Title = 'Contact-View',
		@Description = 'This access allows a user to view non-secured contact data, secured data will be masked'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription)
		VALUES(@SecurityID, @Title, @Description)

	SELECT @SecurityID = -103,
		@Title = 'Contact-ViewSecure',
		@Description = 'This access provides user access to view unmasked Secured details of a contact such as SS# TIN DOB or any other field defined as Secure'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription)
		VALUES(@SecurityID, @Title, @Description)

	SELECT @SecurityID = -104,
		@Title = 'Contact-Add',
		@Description = 'This access provides user access to Add Contacts'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription)
		VALUES(@SecurityID, @Title, @Description)

	SELECT @SecurityID = -105,
		@Title = 'Contact-Edit',
		@Description = 'This access provide user ability to Edit contact data, users unable to view secure information will be unable to update it either.'
		, @HierachySpecific = 0
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription)
		VALUES(@SecurityID, @Title, @Description)
	
	SELECT @SecurityID = -106,
		@Title = 'Contact-Delete',
		@Description = 'This access provide user ability to delete a contact'
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription)
		VALUES(@SecurityID, @Title, @Description)

	SELECT @SecurityID = -107,
		@Title = 'Contact-Audit',
		@Description = 'This access provide user ability to view the history of a Contact'
	IF EXISTS (SELECT 1 FROM dbo.SecurityAccess WHERE SAC_SecurityAccessID = @SecurityID)
		UPDATE dbo.SecurityAccess 
		SET SAC_AccessTitle = @Title
			, SAC_AccessDescription = @Description 
		WHERE SAC_SecurityAccessID = @SecurityID 
	ELSE
		INSERT INTO dbo.SecurityAccess(SAC_SecurityAccessID, SAC_AccessTitle,
									SAC_AccessDescription)
		VALUES(@SecurityID, @Title, @Description)
*/	

	SET IDENTITY_INSERT dbo.SecurityAccess OFF
END
GO
